package br.com.stefanini.provajava.model.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import br.com.stefanini.provajava.model.Experiencia;
import br.com.stefanini.provajava.model.Profissional;
import br.com.stefanini.provajava.model.util.DataBaseUtil;

public class ProfissionalDAO {

	private Connection connection;
	private ExperienciaDAO experienciaDAO;

	public ProfissionalDAO() {
		connection = DataBaseUtil.getConnection();
		experienciaDAO = new ExperienciaDAO();
	}

	public void addProfissional(Profissional profissional) {
		try {
			PreparedStatement preparedStatement = connection
					.prepareStatement("INSERT INTO TB_PROFISSIONAL (ID_PROFISSIONAL, ST_NOME, DT_NASCIMENTO, ST_EMAIL) VALUES (SQ_PROFISSIONAL.NEXTVAL, ?, ?, ? )");
			preparedStatement.setString(1, profissional.getNome());
			preparedStatement.setDate(2, new java.sql.Date(profissional
					.getNascimento().getTime()));
			preparedStatement.setString(3, profissional.getEmail());
			preparedStatement.executeUpdate();

			preparedStatement = connection
					.prepareStatement("SELECT SQ_PROFISSIONAL.CURRVAL FROM DUAL");
			ResultSet rs = preparedStatement.executeQuery();
			if (rs.next()) {
				profissional.setId(rs.getInt("CURRVAL"));
			}

			for (Experiencia experiencia : profissional.getExperiencias()) {
				experienciaDAO
						.addExperiencia(profissional.getId(), experiencia);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void deleteProfissional(Integer idProfissional) {
		try {
			PreparedStatement preparedStatement = connection
					.prepareStatement("DELETE FROM TB_PROFISSIONAL WHERE ID_PROFISSIONAL = ?");
			preparedStatement.setInt(1, idProfissional);
			preparedStatement.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void updateProfissional(Profissional profissional) {
		try {
			PreparedStatement preparedStatement = connection
					.prepareStatement("UPDATE TB_PROFISSIONAL SET ST_NOME = ?, DT_NASCIMENTO = ?, ST_EMAIL = ? WHERE ID_PROFISSIONAL = ?");
			preparedStatement.setString(1, profissional.getNome());
			preparedStatement.setDate(2, new java.sql.Date(profissional
					.getNascimento().getTime()));
			preparedStatement.setString(3, profissional.getEmail());
			preparedStatement.setInt(4, profissional.getId());
			preparedStatement.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public List<Profissional> getAllProfissionais() {
		List<Profissional> retorno = new ArrayList<Profissional>();
		try {
			Statement statement = connection.createStatement();
			ResultSet rs = statement
					.executeQuery("SELECT * FROM TB_PROFISSIONAL");
			while (rs.next()) {
				Profissional profissional = new Profissional();
				profissional.setId(rs.getInt("ID_PROFISSIONAL"));
				profissional.setNome(rs.getString("ST_NOME"));
				profissional.setNascimento(rs.getDate("DT_NASCIMENTO"));
				profissional.setEmail(rs.getString("ST_EMAIL"));
				retorno.add(profissional);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return retorno;
	}

	public Profissional getProfissionalById(Integer idProfissional) {
		Profissional profissional = new Profissional();
		try {
			PreparedStatement preparedStatement = connection
					.prepareStatement("SELECT * FROM TB_PROFISSIONAL WHERE ID_PROFISSIONAL = ?");
			preparedStatement.setInt(1, idProfissional);
			ResultSet rs = preparedStatement.executeQuery();
			if (rs.next()) {
				profissional.setId(rs.getInt("ID_PROFISSIONAL"));
				profissional.setNome(rs.getString("ST_NOME"));
				profissional.setNascimento(rs.getDate("DT_NASCIMENTO"));
				profissional.setEmail(rs.getString("ST_EMAIL"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return profissional;
	}
}
